IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_config]') AND type in (N'U')) DROP TABLE [dbo].[sys_config];
CREATE TABLE [dbo].[sys_config](
    config_id INT IDENTITY(1,1)
    config_name VARCHAR(100),
    config_key VARCHAR(100),
    config_value VARCHAR(500),
    config_type CHAR(1) DEFAULT  'N',
    create_by VARCHAR(64),
    create_time DATETIME,
    update_by VARCHAR(64),
    update_time DATETIME,
    remark VARCHAR(500),
    PRIMARY KEY (config_id)
);

EXEC sp_addextendedproperty 'MS_Description', '参数配置表;', 'SCHEMA', dbo, 'table', sys_config, null, null;
EXEC sp_addextendedproperty 'MS_Description', '参数主键', 'SCHEMA', dbo, 'table', sys_config, 'column', config_id;
EXEC sp_addextendedproperty 'MS_Description', '参数名称', 'SCHEMA', dbo, 'table', sys_config, 'column', config_name;
EXEC sp_addextendedproperty 'MS_Description', '参数键名', 'SCHEMA', dbo, 'table', sys_config, 'column', config_key;
EXEC sp_addextendedproperty 'MS_Description', '参数键值', 'SCHEMA', dbo, 'table', sys_config, 'column', config_value;
EXEC sp_addextendedproperty 'MS_Description', '系统内置（Y是 N否）', 'SCHEMA', dbo, 'table', sys_config, 'column', config_type;
EXEC sp_addextendedproperty 'MS_Description', '创建者', 'SCHEMA', dbo, 'table', sys_config, 'column', create_by;
EXEC sp_addextendedproperty 'MS_Description', '创建时间', 'SCHEMA', dbo, 'table', sys_config, 'column', create_time;
EXEC sp_addextendedproperty 'MS_Description', '更新者', 'SCHEMA', dbo, 'table', sys_config, 'column', update_by;
EXEC sp_addextendedproperty 'MS_Description', '更新时间', 'SCHEMA', dbo, 'table', sys_config, 'column', update_time;
EXEC sp_addextendedproperty 'MS_Description', '备注', 'SCHEMA', dbo, 'table', sys_config, 'column', remark;

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_dept]') AND type in (N'U')) DROP TABLE [dbo].[sys_dept];
CREATE TABLE [dbo].[sys_dept](
    dept_id INT IDENTITY(1,1)
    parent_id BIGINT DEFAULT  0,
    ancestors VARCHAR(50),
    dept_name VARCHAR(30),
    order_num INT DEFAULT  0,
    leader VARCHAR(20),
    phone VARCHAR(11),
    email VARCHAR(50),
    status CHAR(1) DEFAULT  '0',
    del_flag CHAR(1) DEFAULT  '0',
    create_by VARCHAR(64),
    create_time DATETIME,
    update_by VARCHAR(64),
    update_time DATETIME,
    PRIMARY KEY (dept_id)
);

EXEC sp_addextendedproperty 'MS_Description', '部门表;', 'SCHEMA', dbo, 'table', sys_dept, null, null;
EXEC sp_addextendedproperty 'MS_Description', '部门id', 'SCHEMA', dbo, 'table', sys_dept, 'column', dept_id;
EXEC sp_addextendedproperty 'MS_Description', '父部门id', 'SCHEMA', dbo, 'table', sys_dept, 'column', parent_id;
EXEC sp_addextendedproperty 'MS_Description', '祖级列表', 'SCHEMA', dbo, 'table', sys_dept, 'column', ancestors;
EXEC sp_addextendedproperty 'MS_Description', '部门名称', 'SCHEMA', dbo, 'table', sys_dept, 'column', dept_name;
EXEC sp_addextendedproperty 'MS_Description', '显示顺序', 'SCHEMA', dbo, 'table', sys_dept, 'column', order_num;
EXEC sp_addextendedproperty 'MS_Description', '负责人', 'SCHEMA', dbo, 'table', sys_dept, 'column', leader;
EXEC sp_addextendedproperty 'MS_Description', '联系电话', 'SCHEMA', dbo, 'table', sys_dept, 'column', phone;
EXEC sp_addextendedproperty 'MS_Description', '邮箱', 'SCHEMA', dbo, 'table', sys_dept, 'column', email;
EXEC sp_addextendedproperty 'MS_Description', '部门状态（0正常 1停用）', 'SCHEMA', dbo, 'table', sys_dept, 'column', status;
EXEC sp_addextendedproperty 'MS_Description', '删除标志（0代表存在 2代表删除）', 'SCHEMA', dbo, 'table', sys_dept, 'column', del_flag;
EXEC sp_addextendedproperty 'MS_Description', '创建者', 'SCHEMA', dbo, 'table', sys_dept, 'column', create_by;
EXEC sp_addextendedproperty 'MS_Description', '创建时间', 'SCHEMA', dbo, 'table', sys_dept, 'column', create_time;
EXEC sp_addextendedproperty 'MS_Description', '更新者', 'SCHEMA', dbo, 'table', sys_dept, 'column', update_by;
EXEC sp_addextendedproperty 'MS_Description', '更新时间', 'SCHEMA', dbo, 'table', sys_dept, 'column', update_time;

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_dict_data]') AND type in (N'U')) DROP TABLE [dbo].[sys_dict_data];
CREATE TABLE [dbo].[sys_dict_data](
    dict_code INT IDENTITY(1,1)
    dict_sort INT DEFAULT  0,
    dict_label VARCHAR(100),
    dict_value VARCHAR(100),
    dict_type VARCHAR(100),
    css_class VARCHAR(100),
    list_class VARCHAR(100),
    is_default CHAR(1) DEFAULT  'N',
    status CHAR(1) DEFAULT  '0',
    create_by VARCHAR(64),
    create_time DATETIME,
    update_by VARCHAR(64),
    update_time DATETIME,
    remark VARCHAR(500),
    PRIMARY KEY (dict_code)
);

EXEC sp_addextendedproperty 'MS_Description', '字典数据表;', 'SCHEMA', dbo, 'table', sys_dict_data, null, null;
EXEC sp_addextendedproperty 'MS_Description', '字典编码', 'SCHEMA', dbo, 'table', sys_dict_data, 'column', dict_code;
EXEC sp_addextendedproperty 'MS_Description', '字典排序', 'SCHEMA', dbo, 'table', sys_dict_data, 'column', dict_sort;
EXEC sp_addextendedproperty 'MS_Description', '字典标签', 'SCHEMA', dbo, 'table', sys_dict_data, 'column', dict_label;
EXEC sp_addextendedproperty 'MS_Description', '字典键值', 'SCHEMA', dbo, 'table', sys_dict_data, 'column', dict_value;
EXEC sp_addextendedproperty 'MS_Description', '字典类型', 'SCHEMA', dbo, 'table', sys_dict_data, 'column', dict_type;
EXEC sp_addextendedproperty 'MS_Description', '样式属性（其他样式扩展）', 'SCHEMA', dbo, 'table', sys_dict_data, 'column', css_class;
EXEC sp_addextendedproperty 'MS_Description', '表格回显样式', 'SCHEMA', dbo, 'table', sys_dict_data, 'column', list_class;
EXEC sp_addextendedproperty 'MS_Description', '是否默认（Y是 N否）', 'SCHEMA', dbo, 'table', sys_dict_data, 'column', is_default;
EXEC sp_addextendedproperty 'MS_Description', '状态（0正常 1停用）', 'SCHEMA', dbo, 'table', sys_dict_data, 'column', status;
EXEC sp_addextendedproperty 'MS_Description', '创建者', 'SCHEMA', dbo, 'table', sys_dict_data, 'column', create_by;
EXEC sp_addextendedproperty 'MS_Description', '创建时间', 'SCHEMA', dbo, 'table', sys_dict_data, 'column', create_time;
EXEC sp_addextendedproperty 'MS_Description', '更新者', 'SCHEMA', dbo, 'table', sys_dict_data, 'column', update_by;
EXEC sp_addextendedproperty 'MS_Description', '更新时间', 'SCHEMA', dbo, 'table', sys_dict_data, 'column', update_time;
EXEC sp_addextendedproperty 'MS_Description', '备注', 'SCHEMA', dbo, 'table', sys_dict_data, 'column', remark;

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_dict_type]') AND type in (N'U')) DROP TABLE [dbo].[sys_dict_type];
CREATE TABLE [dbo].[sys_dict_type](
    dict_id INT IDENTITY(1,1)
    dict_name VARCHAR(100),
    dict_type VARCHAR(100),
    status CHAR(1) DEFAULT  '0',
    create_by VARCHAR(64),
    create_time DATETIME,
    update_by VARCHAR(64),
    update_time DATETIME,
    remark VARCHAR(500),
    PRIMARY KEY (dict_id)
);

EXEC sp_addextendedproperty 'MS_Description', '字典类型表;', 'SCHEMA', dbo, 'table', sys_dict_type, null, null;
EXEC sp_addextendedproperty 'MS_Description', '字典主键', 'SCHEMA', dbo, 'table', sys_dict_type, 'column', dict_id;
EXEC sp_addextendedproperty 'MS_Description', '字典名称', 'SCHEMA', dbo, 'table', sys_dict_type, 'column', dict_name;
EXEC sp_addextendedproperty 'MS_Description', '字典类型', 'SCHEMA', dbo, 'table', sys_dict_type, 'column', dict_type;
EXEC sp_addextendedproperty 'MS_Description', '状态（0正常 1停用）', 'SCHEMA', dbo, 'table', sys_dict_type, 'column', status;
EXEC sp_addextendedproperty 'MS_Description', '创建者', 'SCHEMA', dbo, 'table', sys_dict_type, 'column', create_by;
EXEC sp_addextendedproperty 'MS_Description', '创建时间', 'SCHEMA', dbo, 'table', sys_dict_type, 'column', create_time;
EXEC sp_addextendedproperty 'MS_Description', '更新者', 'SCHEMA', dbo, 'table', sys_dict_type, 'column', update_by;
EXEC sp_addextendedproperty 'MS_Description', '更新时间', 'SCHEMA', dbo, 'table', sys_dict_type, 'column', update_time;
EXEC sp_addextendedproperty 'MS_Description', '备注', 'SCHEMA', dbo, 'table', sys_dict_type, 'column', remark;


CREATE UNIQUE INDEX dict_type ON sys_dict_type(dict_type);

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_job]') AND type in (N'U')) DROP TABLE [dbo].[sys_job];
CREATE TABLE [dbo].[sys_job](
    job_id INT IDENTITY(1,1)
    job_name VARCHAR(64) NOT NULL,
    job_group VARCHAR(64) NOT NULL DEFAULT  'DEFAULT',
    invoke_target VARCHAR(500) NOT NULL,
    cron_expression VARCHAR(255),
    misfire_policy VARCHAR(20) DEFAULT  '3',
    concurrent CHAR(1) DEFAULT  '1',
    status CHAR(1) DEFAULT  '0',
    create_by VARCHAR(64),
    create_time DATETIME,
    update_by VARCHAR(64),
    update_time DATETIME,
    remark VARCHAR(500),
    PRIMARY KEY (job_id,job_name,job_group)
);

EXEC sp_addextendedproperty 'MS_Description', '定时任务调度表;', 'SCHEMA', dbo, 'table', sys_job, null, null;
EXEC sp_addextendedproperty 'MS_Description', '任务ID', 'SCHEMA', dbo, 'table', sys_job, 'column', job_id;
EXEC sp_addextendedproperty 'MS_Description', '任务名称', 'SCHEMA', dbo, 'table', sys_job, 'column', job_name;
EXEC sp_addextendedproperty 'MS_Description', '任务组名', 'SCHEMA', dbo, 'table', sys_job, 'column', job_group;
EXEC sp_addextendedproperty 'MS_Description', '调用目标字符串', 'SCHEMA', dbo, 'table', sys_job, 'column', invoke_target;
EXEC sp_addextendedproperty 'MS_Description', 'cron执行表达式', 'SCHEMA', dbo, 'table', sys_job, 'column', cron_expression;
EXEC sp_addextendedproperty 'MS_Description', '计划执行错误策略（1立即执行 2执行一次 3放弃执行）', 'SCHEMA', dbo, 'table', sys_job, 'column', misfire_policy;
EXEC sp_addextendedproperty 'MS_Description', '是否并发执行（0允许 1禁止）', 'SCHEMA', dbo, 'table', sys_job, 'column', concurrent;
EXEC sp_addextendedproperty 'MS_Description', '状态（0正常 1暂停）', 'SCHEMA', dbo, 'table', sys_job, 'column', status;
EXEC sp_addextendedproperty 'MS_Description', '创建者', 'SCHEMA', dbo, 'table', sys_job, 'column', create_by;
EXEC sp_addextendedproperty 'MS_Description', '创建时间', 'SCHEMA', dbo, 'table', sys_job, 'column', create_time;
EXEC sp_addextendedproperty 'MS_Description', '更新者', 'SCHEMA', dbo, 'table', sys_job, 'column', update_by;
EXEC sp_addextendedproperty 'MS_Description', '更新时间', 'SCHEMA', dbo, 'table', sys_job, 'column', update_time;
EXEC sp_addextendedproperty 'MS_Description', '备注信息', 'SCHEMA', dbo, 'table', sys_job, 'column', remark;

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_job_log]') AND type in (N'U')) DROP TABLE [dbo].[sys_job_log];
CREATE TABLE [dbo].[sys_job_log](
    job_log_id INT IDENTITY(1,1)
    job_name VARCHAR(64) NOT NULL,
    job_group VARCHAR(64) NOT NULL,
    invoke_target VARCHAR(500) NOT NULL,
    job_message VARCHAR(500),
    status CHAR(1) DEFAULT  '0',
    exception_info VARCHAR(2000),
    create_time DATETIME,
    PRIMARY KEY (job_log_id)
);

EXEC sp_addextendedproperty 'MS_Description', '定时任务调度日志表;', 'SCHEMA', dbo, 'table', sys_job_log, null, null;
EXEC sp_addextendedproperty 'MS_Description', '任务日志ID', 'SCHEMA', dbo, 'table', sys_job_log, 'column', job_log_id;
EXEC sp_addextendedproperty 'MS_Description', '任务名称', 'SCHEMA', dbo, 'table', sys_job_log, 'column', job_name;
EXEC sp_addextendedproperty 'MS_Description', '任务组名', 'SCHEMA', dbo, 'table', sys_job_log, 'column', job_group;
EXEC sp_addextendedproperty 'MS_Description', '调用目标字符串', 'SCHEMA', dbo, 'table', sys_job_log, 'column', invoke_target;
EXEC sp_addextendedproperty 'MS_Description', '日志信息', 'SCHEMA', dbo, 'table', sys_job_log, 'column', job_message;
EXEC sp_addextendedproperty 'MS_Description', '执行状态（0正常 1失败）', 'SCHEMA', dbo, 'table', sys_job_log, 'column', status;
EXEC sp_addextendedproperty 'MS_Description', '异常信息', 'SCHEMA', dbo, 'table', sys_job_log, 'column', exception_info;
EXEC sp_addextendedproperty 'MS_Description', '创建时间', 'SCHEMA', dbo, 'table', sys_job_log, 'column', create_time;

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_logininfor]') AND type in (N'U')) DROP TABLE [dbo].[sys_logininfor];
CREATE TABLE [dbo].[sys_logininfor](
    info_id INT IDENTITY(1,1)
    user_name VARCHAR(50),
    ipaddr VARCHAR(128),
    login_location VARCHAR(255),
    browser VARCHAR(50),
    os VARCHAR(50),
    status CHAR(1) DEFAULT  '0',
    msg VARCHAR(255),
    login_time DATETIME,
    PRIMARY KEY (info_id)
);

EXEC sp_addextendedproperty 'MS_Description', '系统访问记录;', 'SCHEMA', dbo, 'table', sys_logininfor, null, null;
EXEC sp_addextendedproperty 'MS_Description', '访问ID', 'SCHEMA', dbo, 'table', sys_logininfor, 'column', info_id;
EXEC sp_addextendedproperty 'MS_Description', '用户账号', 'SCHEMA', dbo, 'table', sys_logininfor, 'column', user_name;
EXEC sp_addextendedproperty 'MS_Description', '登录IP地址', 'SCHEMA', dbo, 'table', sys_logininfor, 'column', ipaddr;
EXEC sp_addextendedproperty 'MS_Description', '登录地点', 'SCHEMA', dbo, 'table', sys_logininfor, 'column', login_location;
EXEC sp_addextendedproperty 'MS_Description', '浏览器类型', 'SCHEMA', dbo, 'table', sys_logininfor, 'column', browser;
EXEC sp_addextendedproperty 'MS_Description', '操作系统', 'SCHEMA', dbo, 'table', sys_logininfor, 'column', os;
EXEC sp_addextendedproperty 'MS_Description', '登录状态（0成功 1失败）', 'SCHEMA', dbo, 'table', sys_logininfor, 'column', status;
EXEC sp_addextendedproperty 'MS_Description', '提示消息', 'SCHEMA', dbo, 'table', sys_logininfor, 'column', msg;
EXEC sp_addextendedproperty 'MS_Description', '访问时间', 'SCHEMA', dbo, 'table', sys_logininfor, 'column', login_time;


CREATE INDEX idx_sys_logininfor_lt ON sys_logininfor(login_time);
CREATE INDEX idx_sys_logininfor_s ON sys_logininfor(status);

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_menu]') AND type in (N'U')) DROP TABLE [dbo].[sys_menu];
CREATE TABLE [dbo].[sys_menu](
    menu_id INT IDENTITY(1,1)
    menu_name VARCHAR(50) NOT NULL,
    parent_id BIGINT DEFAULT  0,
    order_num INT DEFAULT  0,
    path VARCHAR(200),
    component VARCHAR(255),
    query VARCHAR(255),
    route_name VARCHAR(50),
    is_frame INT DEFAULT  1,
    is_cache INT DEFAULT  0,
    menu_type CHAR(1),
    visible CHAR(1) DEFAULT  '0',
    status CHAR(1) DEFAULT  '0',
    perms VARCHAR(100),
    icon VARCHAR(100) DEFAULT  '#',
    create_by VARCHAR(64),
    create_time DATETIME,
    update_by VARCHAR(64),
    update_time DATETIME,
    remark VARCHAR(500),
    PRIMARY KEY (menu_id)
);

EXEC sp_addextendedproperty 'MS_Description', '菜单权限表;', 'SCHEMA', dbo, 'table', sys_menu, null, null;
EXEC sp_addextendedproperty 'MS_Description', '菜单ID', 'SCHEMA', dbo, 'table', sys_menu, 'column', menu_id;
EXEC sp_addextendedproperty 'MS_Description', '菜单名称', 'SCHEMA', dbo, 'table', sys_menu, 'column', menu_name;
EXEC sp_addextendedproperty 'MS_Description', '父菜单ID', 'SCHEMA', dbo, 'table', sys_menu, 'column', parent_id;
EXEC sp_addextendedproperty 'MS_Description', '显示顺序', 'SCHEMA', dbo, 'table', sys_menu, 'column', order_num;
EXEC sp_addextendedproperty 'MS_Description', '路由地址', 'SCHEMA', dbo, 'table', sys_menu, 'column', path;
EXEC sp_addextendedproperty 'MS_Description', '组件路径', 'SCHEMA', dbo, 'table', sys_menu, 'column', component;
EXEC sp_addextendedproperty 'MS_Description', '路由参数', 'SCHEMA', dbo, 'table', sys_menu, 'column', query;
EXEC sp_addextendedproperty 'MS_Description', '路由名称', 'SCHEMA', dbo, 'table', sys_menu, 'column', route_name;
EXEC sp_addextendedproperty 'MS_Description', '是否为外链（0是 1否）', 'SCHEMA', dbo, 'table', sys_menu, 'column', is_frame;
EXEC sp_addextendedproperty 'MS_Description', '是否缓存（0缓存 1不缓存）', 'SCHEMA', dbo, 'table', sys_menu, 'column', is_cache;
EXEC sp_addextendedproperty 'MS_Description', '菜单类型（M目录 C菜单 F按钮）', 'SCHEMA', dbo, 'table', sys_menu, 'column', menu_type;
EXEC sp_addextendedproperty 'MS_Description', '菜单状态（0显示 1隐藏）', 'SCHEMA', dbo, 'table', sys_menu, 'column', visible;
EXEC sp_addextendedproperty 'MS_Description', '菜单状态（0正常 1停用）', 'SCHEMA', dbo, 'table', sys_menu, 'column', status;
EXEC sp_addextendedproperty 'MS_Description', '权限标识', 'SCHEMA', dbo, 'table', sys_menu, 'column', perms;
EXEC sp_addextendedproperty 'MS_Description', '菜单图标', 'SCHEMA', dbo, 'table', sys_menu, 'column', icon;
EXEC sp_addextendedproperty 'MS_Description', '创建者', 'SCHEMA', dbo, 'table', sys_menu, 'column', create_by;
EXEC sp_addextendedproperty 'MS_Description', '创建时间', 'SCHEMA', dbo, 'table', sys_menu, 'column', create_time;
EXEC sp_addextendedproperty 'MS_Description', '更新者', 'SCHEMA', dbo, 'table', sys_menu, 'column', update_by;
EXEC sp_addextendedproperty 'MS_Description', '更新时间', 'SCHEMA', dbo, 'table', sys_menu, 'column', update_time;
EXEC sp_addextendedproperty 'MS_Description', '备注', 'SCHEMA', dbo, 'table', sys_menu, 'column', remark;

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_notice]') AND type in (N'U')) DROP TABLE [dbo].[sys_notice];
CREATE TABLE [dbo].[sys_notice](
    notice_id INT IDENTITY(1,1)
    notice_title VARCHAR(50) NOT NULL,
    notice_type CHAR(1) NOT NULL,
    notice_content LONGBLOB,
    status CHAR(1) DEFAULT  '0',
    create_by VARCHAR(64),
    create_time DATETIME,
    update_by VARCHAR(64),
    update_time DATETIME,
    remark VARCHAR(255),
    PRIMARY KEY (notice_id)
);

EXEC sp_addextendedproperty 'MS_Description', '通知公告表;', 'SCHEMA', dbo, 'table', sys_notice, null, null;
EXEC sp_addextendedproperty 'MS_Description', '公告ID', 'SCHEMA', dbo, 'table', sys_notice, 'column', notice_id;
EXEC sp_addextendedproperty 'MS_Description', '公告标题', 'SCHEMA', dbo, 'table', sys_notice, 'column', notice_title;
EXEC sp_addextendedproperty 'MS_Description', '公告类型（1通知 2公告）', 'SCHEMA', dbo, 'table', sys_notice, 'column', notice_type;
EXEC sp_addextendedproperty 'MS_Description', '公告内容', 'SCHEMA', dbo, 'table', sys_notice, 'column', notice_content;
EXEC sp_addextendedproperty 'MS_Description', '公告状态（0正常 1关闭）', 'SCHEMA', dbo, 'table', sys_notice, 'column', status;
EXEC sp_addextendedproperty 'MS_Description', '创建者', 'SCHEMA', dbo, 'table', sys_notice, 'column', create_by;
EXEC sp_addextendedproperty 'MS_Description', '创建时间', 'SCHEMA', dbo, 'table', sys_notice, 'column', create_time;
EXEC sp_addextendedproperty 'MS_Description', '更新者', 'SCHEMA', dbo, 'table', sys_notice, 'column', update_by;
EXEC sp_addextendedproperty 'MS_Description', '更新时间', 'SCHEMA', dbo, 'table', sys_notice, 'column', update_time;
EXEC sp_addextendedproperty 'MS_Description', '备注', 'SCHEMA', dbo, 'table', sys_notice, 'column', remark;

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_oper_log]') AND type in (N'U')) DROP TABLE [dbo].[sys_oper_log];
CREATE TABLE [dbo].[sys_oper_log](
    oper_id INT IDENTITY(1,1)
    title VARCHAR(50),
    business_type INT DEFAULT  0,
    method VARCHAR(200),
    request_method VARCHAR(10),
    operator_type INT DEFAULT  0,
    oper_name VARCHAR(50),
    dept_name VARCHAR(50),
    oper_url VARCHAR(255),
    oper_ip VARCHAR(128),
    oper_location VARCHAR(255),
    oper_param VARCHAR(2000),
    json_result VARCHAR(2000),
    status INT DEFAULT  0,
    error_msg VARCHAR(2000),
    oper_time DATETIME,
    cost_time BIGINT DEFAULT  0,
    PRIMARY KEY (oper_id)
);

EXEC sp_addextendedproperty 'MS_Description', '操作日志记录;', 'SCHEMA', dbo, 'table', sys_oper_log, null, null;
EXEC sp_addextendedproperty 'MS_Description', '日志主键', 'SCHEMA', dbo, 'table', sys_oper_log, 'column', oper_id;
EXEC sp_addextendedproperty 'MS_Description', '模块标题', 'SCHEMA', dbo, 'table', sys_oper_log, 'column', title;
EXEC sp_addextendedproperty 'MS_Description', '业务类型（0其它 1新增 2修改 3删除）', 'SCHEMA', dbo, 'table', sys_oper_log, 'column', business_type;
EXEC sp_addextendedproperty 'MS_Description', '方法名称', 'SCHEMA', dbo, 'table', sys_oper_log, 'column', method;
EXEC sp_addextendedproperty 'MS_Description', '请求方式', 'SCHEMA', dbo, 'table', sys_oper_log, 'column', request_method;
EXEC sp_addextendedproperty 'MS_Description', '操作类别（0其它 1后台用户 2手机端用户）', 'SCHEMA', dbo, 'table', sys_oper_log, 'column', operator_type;
EXEC sp_addextendedproperty 'MS_Description', '操作人员', 'SCHEMA', dbo, 'table', sys_oper_log, 'column', oper_name;
EXEC sp_addextendedproperty 'MS_Description', '部门名称', 'SCHEMA', dbo, 'table', sys_oper_log, 'column', dept_name;
EXEC sp_addextendedproperty 'MS_Description', '请求URL', 'SCHEMA', dbo, 'table', sys_oper_log, 'column', oper_url;
EXEC sp_addextendedproperty 'MS_Description', '主机地址', 'SCHEMA', dbo, 'table', sys_oper_log, 'column', oper_ip;
EXEC sp_addextendedproperty 'MS_Description', '操作地点', 'SCHEMA', dbo, 'table', sys_oper_log, 'column', oper_location;
EXEC sp_addextendedproperty 'MS_Description', '请求参数', 'SCHEMA', dbo, 'table', sys_oper_log, 'column', oper_param;
EXEC sp_addextendedproperty 'MS_Description', '返回参数', 'SCHEMA', dbo, 'table', sys_oper_log, 'column', json_result;
EXEC sp_addextendedproperty 'MS_Description', '操作状态（0正常 1异常）', 'SCHEMA', dbo, 'table', sys_oper_log, 'column', status;
EXEC sp_addextendedproperty 'MS_Description', '错误消息', 'SCHEMA', dbo, 'table', sys_oper_log, 'column', error_msg;
EXEC sp_addextendedproperty 'MS_Description', '操作时间', 'SCHEMA', dbo, 'table', sys_oper_log, 'column', oper_time;
EXEC sp_addextendedproperty 'MS_Description', '消耗时间', 'SCHEMA', dbo, 'table', sys_oper_log, 'column', cost_time;


CREATE INDEX idx_sys_oper_log_bt ON sys_oper_log(business_type);
CREATE INDEX idx_sys_oper_log_ot ON sys_oper_log(oper_time);
CREATE INDEX idx_sys_oper_log_s ON sys_oper_log(status);

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_post]') AND type in (N'U')) DROP TABLE [dbo].[sys_post];
CREATE TABLE [dbo].[sys_post](
    post_id INT IDENTITY(1,1)
    post_code VARCHAR(64) NOT NULL,
    post_name VARCHAR(50) NOT NULL,
    post_sort INT NOT NULL,
    status CHAR(1) NOT NULL,
    create_by VARCHAR(64),
    create_time DATETIME,
    update_by VARCHAR(64),
    update_time DATETIME,
    remark VARCHAR(500),
    PRIMARY KEY (post_id)
);

EXEC sp_addextendedproperty 'MS_Description', '岗位信息表;', 'SCHEMA', dbo, 'table', sys_post, null, null;
EXEC sp_addextendedproperty 'MS_Description', '岗位ID', 'SCHEMA', dbo, 'table', sys_post, 'column', post_id;
EXEC sp_addextendedproperty 'MS_Description', '岗位编码', 'SCHEMA', dbo, 'table', sys_post, 'column', post_code;
EXEC sp_addextendedproperty 'MS_Description', '岗位名称', 'SCHEMA', dbo, 'table', sys_post, 'column', post_name;
EXEC sp_addextendedproperty 'MS_Description', '显示顺序', 'SCHEMA', dbo, 'table', sys_post, 'column', post_sort;
EXEC sp_addextendedproperty 'MS_Description', '状态（0正常 1停用）', 'SCHEMA', dbo, 'table', sys_post, 'column', status;
EXEC sp_addextendedproperty 'MS_Description', '创建者', 'SCHEMA', dbo, 'table', sys_post, 'column', create_by;
EXEC sp_addextendedproperty 'MS_Description', '创建时间', 'SCHEMA', dbo, 'table', sys_post, 'column', create_time;
EXEC sp_addextendedproperty 'MS_Description', '更新者', 'SCHEMA', dbo, 'table', sys_post, 'column', update_by;
EXEC sp_addextendedproperty 'MS_Description', '更新时间', 'SCHEMA', dbo, 'table', sys_post, 'column', update_time;
EXEC sp_addextendedproperty 'MS_Description', '备注', 'SCHEMA', dbo, 'table', sys_post, 'column', remark;

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_role]') AND type in (N'U')) DROP TABLE [dbo].[sys_role];
CREATE TABLE [dbo].[sys_role](
    role_id INT IDENTITY(1,1)
    role_name VARCHAR(30) NOT NULL,
    role_key VARCHAR(100) NOT NULL,
    role_sort INT NOT NULL,
    data_scope CHAR(1) DEFAULT  '1',
    menu_check_strictly TINYINT DEFAULT  1,
    dept_check_strictly TINYINT DEFAULT  1,
    status CHAR(1) NOT NULL,
    del_flag CHAR(1) DEFAULT  '0',
    create_by VARCHAR(64),
    create_time DATETIME,
    update_by VARCHAR(64),
    update_time DATETIME,
    remark VARCHAR(500),
    PRIMARY KEY (role_id)
);

EXEC sp_addextendedproperty 'MS_Description', '角色信息表;', 'SCHEMA', dbo, 'table', sys_role, null, null;
EXEC sp_addextendedproperty 'MS_Description', '角色ID', 'SCHEMA', dbo, 'table', sys_role, 'column', role_id;
EXEC sp_addextendedproperty 'MS_Description', '角色名称', 'SCHEMA', dbo, 'table', sys_role, 'column', role_name;
EXEC sp_addextendedproperty 'MS_Description', '角色权限字符串', 'SCHEMA', dbo, 'table', sys_role, 'column', role_key;
EXEC sp_addextendedproperty 'MS_Description', '显示顺序', 'SCHEMA', dbo, 'table', sys_role, 'column', role_sort;
EXEC sp_addextendedproperty 'MS_Description', '数据范围（1：全部数据权限 2：自定数据权限 3：本部门数据权限 4：本部门及以下数据权限）', 'SCHEMA', dbo, 'table', sys_role, 'column', data_scope;
EXEC sp_addextendedproperty 'MS_Description', '菜单树选择项是否关联显示', 'SCHEMA', dbo, 'table', sys_role, 'column', menu_check_strictly;
EXEC sp_addextendedproperty 'MS_Description', '部门树选择项是否关联显示', 'SCHEMA', dbo, 'table', sys_role, 'column', dept_check_strictly;
EXEC sp_addextendedproperty 'MS_Description', '角色状态（0正常 1停用）', 'SCHEMA', dbo, 'table', sys_role, 'column', status;
EXEC sp_addextendedproperty 'MS_Description', '删除标志（0代表存在 2代表删除）', 'SCHEMA', dbo, 'table', sys_role, 'column', del_flag;
EXEC sp_addextendedproperty 'MS_Description', '创建者', 'SCHEMA', dbo, 'table', sys_role, 'column', create_by;
EXEC sp_addextendedproperty 'MS_Description', '创建时间', 'SCHEMA', dbo, 'table', sys_role, 'column', create_time;
EXEC sp_addextendedproperty 'MS_Description', '更新者', 'SCHEMA', dbo, 'table', sys_role, 'column', update_by;
EXEC sp_addextendedproperty 'MS_Description', '更新时间', 'SCHEMA', dbo, 'table', sys_role, 'column', update_time;
EXEC sp_addextendedproperty 'MS_Description', '备注', 'SCHEMA', dbo, 'table', sys_role, 'column', remark;

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_role_dept]') AND type in (N'U')) DROP TABLE [dbo].[sys_role_dept];
CREATE TABLE [dbo].[sys_role_dept](
    role_id INT NOT NULL,
    dept_id INT NOT NULL,
    PRIMARY KEY (role_id,dept_id)
);

EXEC sp_addextendedproperty 'MS_Description', '角色和部门关联表;', 'SCHEMA', dbo, 'table', sys_role_dept, null, null;
EXEC sp_addextendedproperty 'MS_Description', '角色ID', 'SCHEMA', dbo, 'table', sys_role_dept, 'column', role_id;
EXEC sp_addextendedproperty 'MS_Description', '部门ID', 'SCHEMA', dbo, 'table', sys_role_dept, 'column', dept_id;

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_role_menu]') AND type in (N'U')) DROP TABLE [dbo].[sys_role_menu];
CREATE TABLE [dbo].[sys_role_menu](
    role_id INT NOT NULL,
    menu_id INT NOT NULL,
    PRIMARY KEY (role_id,menu_id)
);

EXEC sp_addextendedproperty 'MS_Description', '角色和菜单关联表;', 'SCHEMA', dbo, 'table', sys_role_menu, null, null;
EXEC sp_addextendedproperty 'MS_Description', '角色ID', 'SCHEMA', dbo, 'table', sys_role_menu, 'column', role_id;
EXEC sp_addextendedproperty 'MS_Description', '菜单ID', 'SCHEMA', dbo, 'table', sys_role_menu, 'column', menu_id;

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_user]') AND type in (N'U')) DROP TABLE [dbo].[sys_user];
CREATE TABLE [dbo].[sys_user](
    user_id INT IDENTITY(1,1)
    dept_id BIGINT,
    user_name VARCHAR(30) NOT NULL,
    nick_name VARCHAR(30) NOT NULL,
    user_type VARCHAR(2) DEFAULT  '00',
    email VARCHAR(50),
    phonenumber VARCHAR(11),
    sex CHAR(1) DEFAULT  '0',
    avatar VARCHAR(100),
    password VARCHAR(100),
    status CHAR(1) DEFAULT  '0',
    del_flag CHAR(1) DEFAULT  '0',
    login_ip VARCHAR(128),
    login_date DATETIME,
    create_by VARCHAR(64),
    create_time DATETIME,
    update_by VARCHAR(64),
    update_time DATETIME,
    remark VARCHAR(500),
    PRIMARY KEY (user_id)
);

EXEC sp_addextendedproperty 'MS_Description', '用户信息表;', 'SCHEMA', dbo, 'table', sys_user, null, null;
EXEC sp_addextendedproperty 'MS_Description', '用户ID', 'SCHEMA', dbo, 'table', sys_user, 'column', user_id;
EXEC sp_addextendedproperty 'MS_Description', '部门ID', 'SCHEMA', dbo, 'table', sys_user, 'column', dept_id;
EXEC sp_addextendedproperty 'MS_Description', '用户账号', 'SCHEMA', dbo, 'table', sys_user, 'column', user_name;
EXEC sp_addextendedproperty 'MS_Description', '用户昵称', 'SCHEMA', dbo, 'table', sys_user, 'column', nick_name;
EXEC sp_addextendedproperty 'MS_Description', '用户类型（00系统用户）', 'SCHEMA', dbo, 'table', sys_user, 'column', user_type;
EXEC sp_addextendedproperty 'MS_Description', '用户邮箱', 'SCHEMA', dbo, 'table', sys_user, 'column', email;
EXEC sp_addextendedproperty 'MS_Description', '手机号码', 'SCHEMA', dbo, 'table', sys_user, 'column', phonenumber;
EXEC sp_addextendedproperty 'MS_Description', '用户性别（0男 1女 2未知）', 'SCHEMA', dbo, 'table', sys_user, 'column', sex;
EXEC sp_addextendedproperty 'MS_Description', '头像地址', 'SCHEMA', dbo, 'table', sys_user, 'column', avatar;
EXEC sp_addextendedproperty 'MS_Description', '密码', 'SCHEMA', dbo, 'table', sys_user, 'column', password;
EXEC sp_addextendedproperty 'MS_Description', '帐号状态（0正常 1停用）', 'SCHEMA', dbo, 'table', sys_user, 'column', status;
EXEC sp_addextendedproperty 'MS_Description', '删除标志（0代表存在 2代表删除）', 'SCHEMA', dbo, 'table', sys_user, 'column', del_flag;
EXEC sp_addextendedproperty 'MS_Description', '最后登录IP', 'SCHEMA', dbo, 'table', sys_user, 'column', login_ip;
EXEC sp_addextendedproperty 'MS_Description', '最后登录时间', 'SCHEMA', dbo, 'table', sys_user, 'column', login_date;
EXEC sp_addextendedproperty 'MS_Description', '创建者', 'SCHEMA', dbo, 'table', sys_user, 'column', create_by;
EXEC sp_addextendedproperty 'MS_Description', '创建时间', 'SCHEMA', dbo, 'table', sys_user, 'column', create_time;
EXEC sp_addextendedproperty 'MS_Description', '更新者', 'SCHEMA', dbo, 'table', sys_user, 'column', update_by;
EXEC sp_addextendedproperty 'MS_Description', '更新时间', 'SCHEMA', dbo, 'table', sys_user, 'column', update_time;
EXEC sp_addextendedproperty 'MS_Description', '备注', 'SCHEMA', dbo, 'table', sys_user, 'column', remark;

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_user_post]') AND type in (N'U')) DROP TABLE [dbo].[sys_user_post];
CREATE TABLE [dbo].[sys_user_post](
    user_id INT NOT NULL,
    post_id INT NOT NULL,
    PRIMARY KEY (user_id,post_id)
);

EXEC sp_addextendedproperty 'MS_Description', '用户与岗位关联表;', 'SCHEMA', dbo, 'table', sys_user_post, null, null;
EXEC sp_addextendedproperty 'MS_Description', '用户ID', 'SCHEMA', dbo, 'table', sys_user_post, 'column', user_id;
EXEC sp_addextendedproperty 'MS_Description', '岗位ID', 'SCHEMA', dbo, 'table', sys_user_post, 'column', post_id;

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_user_role]') AND type in (N'U')) DROP TABLE [dbo].[sys_user_role];
CREATE TABLE [dbo].[sys_user_role](
    user_id INT NOT NULL,
    role_id INT NOT NULL,
    PRIMARY KEY (user_id,role_id)
);

EXEC sp_addextendedproperty 'MS_Description', '用户和角色关联表;', 'SCHEMA', dbo, 'table', sys_user_role, null, null;
EXEC sp_addextendedproperty 'MS_Description', '用户ID', 'SCHEMA', dbo, 'table', sys_user_role, 'column', user_id;
EXEC sp_addextendedproperty 'MS_Description', '角色ID', 'SCHEMA', dbo, 'table', sys_user_role, 'column', role_id;

